
package com.scalar.chiptrack.adaptor.dao;

import java.util.ArrayList;
import java.util.List;
import java.util.Iterator;
import java.util.LinkedHashMap;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.CallableStatement;

import com.scalar.chiptrack.utils.Logger;
import com.scalar.chiptrack.utils.SqlQueryMappings;
import com.scalar.chiptrack.utils.StringUtils;
import com.scalar.chiptrack.db.DataSource;
import com.scalar.chiptrack.db.DBTransaction;
import com.scalar.ScalarException;
import com.scalar.chiptrack.adaptor.AdaptorConstants;
import com.scalar.chiptrack.adaptor.FabSortLot;
import com.scalar.chiptrack.adaptor.SortLotBinData;
import com.scalar.chiptrack.operations.process.BinSetup;


public final class FabSortAdaptorDAO implements AdaptorConstants
 {


    public static void saveFabLot (DBTransaction dbTransaction, Iterator fabLotIterator, String psType, String vendorName) throws ScalarException
    {
        saveFabLot (dbTransaction, fabLotIterator, psType, vendorName, true);
    }

    public static void saveFabLot (DBTransaction dbTransaction, Iterator fabLotIterator, String psType, String vendorName, boolean closeLots ) throws ScalarException
    {
        Connection conn = null;
        PreparedStatement pstmt = null;

        boolean success = false;

        try
        {
            // Get the connection from regular datasource if its not in a manual transaction
            if (dbTransaction == null)
            {
                conn = DataSource.getConnection();
            }
            else
            {
                conn = dbTransaction.getConnection();
            }

            if (closeLots == true )
            {

                if ( vendorName != null )
                {
                    pstmt = conn.prepareStatement (SqlQueryMappings.get ("adaptor.closeAllWipInventoryLots"));
                    pstmt.setString (1, psType );
                    pstmt.setString (2, vendorName);
                    pstmt.executeUpdate();
                    pstmt.close();
                    pstmt = null;
                }
                else
                {
                    pstmt = conn.prepareStatement (SqlQueryMappings.get ("adaptor.closeAllWipInventoryLots"));
                    pstmt.setString (1, psType );
                    pstmt.setString (2, VENDOR_NAME_CMI);
                    pstmt.executeUpdate();
                    pstmt.close();
                    pstmt = null;

                    pstmt = conn.prepareStatement (SqlQueryMappings.get ("adaptor.closeAllWipInventoryLots"));
                     pstmt.setString (1, psType );
                    pstmt.setString (2, VENDOR_NAME_CML);
                    pstmt.executeUpdate();
                    pstmt.close();
                    pstmt = null;

                    pstmt = conn.prepareStatement (SqlQueryMappings.get ("adaptor.closeAllWipInventoryLots"));
                    pstmt.setString (1, psType );
                    pstmt.setString (2, VENDOR_NAME_CMT);
                    pstmt.executeUpdate();
                    pstmt.close();
                    pstmt = null;
               }

            }

            // Step 2
            // Run new values
            pstmt = conn.prepareStatement (SqlQueryMappings.get ("adaptor.createUpdateFabLot"));

            FabSortLot wipSummary = null;

            while (fabLotIterator.hasNext() )
            {
                wipSummary = (FabSortLot) fabLotIterator.next();

                if (wipSummary.getLotId() == null) continue;

                log ("Fab Info: " + wipSummary);

                pstmt.setString (1, wipSummary.getCustProduct() );
                pstmt.setString (2, wipSummary.getLotId());
                pstmt.setString (3, wipSummary.getLotStatus());
                pstmt.setString (4, wipSummary.getQtyIn());
                pstmt.setString (5, wipSummary.getPoNumber());
                pstmt.setString (6, wipSummary.getDateIn());
                pstmt.setString (7, wipSummary.getStage());
                pstmt.setString (8, wipSummary.getStagePosition());
                pstmt.setString (9, wipSummary.getForcastedDateOut());
                pstmt.setString (10, wipSummary.getVendorName());
                pstmt.setString (11, wipSummary.getQtyOut());
                pstmt.setString (12, wipSummary.getDateOut());
                pstmt.setString (13, wipSummary.getOutLookDate());
                pstmt.setString (14, wipSummary.getWaferID());
                pstmt.setString (15, wipSummary.getPsType() );
                pstmt.setString (16, wipSummary.getLotCategory() );


                pstmt.addBatch();
            }

            log ("\nSaving Fab Lot Information...");
            pstmt.executeBatch();
            log ("done");

            success = true;
        }
        catch (Exception sqle)
        {
            sqle.printStackTrace();
            throw new ScalarException ("Could not Save FAB lot information", sqle);
        }

        finally
        {
            // Commit/Rollback here it selef -- if its not in a manual transaction
            if (dbTransaction == null)
            {
                try
                {
                    if (conn != null)
                    {
                        if (success == true)
                        {
                            conn.commit();
                        }
                        else
                        {
                            conn.rollback();
                        }
                    }
                }
                catch (SQLException ignore) {}
            }

            if (pstmt != null)
            {
                try
                {
                    pstmt.close();
                }
                catch (SQLException ignore){}
            }
            if (conn != null)
            {
                try
                {
                    conn.close();
                }
                catch (SQLException ignore){}
            }
        }
    }

    public static void saveSortLot (DBTransaction dbTransaction, Iterator sortLotIterator, String psType , String vendorName) throws ScalarException
    {
        saveSortLot (dbTransaction, sortLotIterator, psType, vendorName, true );
    }

    public static void saveSortLot (DBTransaction dbTransaction, Iterator sortLotIterator, String psType , String vendorName, boolean closeLots ) throws ScalarException
    {
        Connection conn1        = null;
        PreparedStatement pstmt = null;
        boolean success = false;


        try
        {
            // Get the connection from regular datasource if its not in a manual transaction
            if (dbTransaction == null)
            {
                conn1 = DataSource.getConnection();
            }
            else
            {
                conn1 = dbTransaction.getConnection();
            }

            if (closeLots == true)
            {
                // Step 1
                // Close all
                if ( vendorName != null )
                {
                    pstmt = conn1.prepareStatement (SqlQueryMappings.get ("adaptor.closeAllWipInventoryLots"));
                    pstmt.setString (1, psType);
                    pstmt.setString (2, vendorName);
                    pstmt.executeUpdate();

                    pstmt.close();
                    pstmt =  null;
                }
                else
                {
                    pstmt = conn1.prepareStatement(SqlQueryMappings.get("adaptor.closeAllWipInventoryLots"));
                    pstmt.setString(1, psType);
                    pstmt.setString(2, VENDOR_NAME_SCS);
                    pstmt.executeUpdate();
                    pstmt.close();
                    pstmt = null;

                    pstmt = conn1.prepareStatement(SqlQueryMappings.get("adaptor.closeAllWipInventoryLots"));
                    pstmt.setString(1, psType);
                    pstmt.setString(2, VENDOR_NAME_SCK);
                    pstmt.executeUpdate();
                    pstmt.close();
                    pstmt = null;

                    pstmt = conn1.prepareStatement(SqlQueryMappings.get("adaptor.closeAllWipInventoryLots"));
                    pstmt.setString(1, psType);
                    pstmt.setString(2, VENDOR_NAME_SCC);
                    pstmt.executeUpdate();
                    pstmt.close();
                    pstmt = null;

                    pstmt = conn1.prepareStatement(SqlQueryMappings.get("adaptor.closeAllWipInventoryLots"));
                    pstmt.setString(1, psType);
                    pstmt.setString(2, VENDOR_NAME_SCM );
                    pstmt.executeUpdate();
                    pstmt.close();
                    pstmt = null;
                }
            }
            // Step 2
            // Run new values
            pstmt = conn1.prepareStatement (SqlQueryMappings.get ("adaptor.createUpdateSortLot"));

            FabSortLot wipSummary = null;

            while ( sortLotIterator.hasNext() )
            {
                wipSummary = (FabSortLot) sortLotIterator.next();

                log ("Sort Info: " + wipSummary);

                if (wipSummary.getLotId() == null) continue;

                pstmt.setString (1, wipSummary.getCustProduct());
                pstmt.setString (2, wipSummary.getLotId());
                pstmt.setString (3, wipSummary.getLotStatus());
                pstmt.setString (4, wipSummary.getQtyIn());
                pstmt.setString (5, wipSummary.getPoNumber());
                pstmt.setString (6, wipSummary.getDateIn());
                pstmt.setString (7, wipSummary.getStage());
                pstmt.setString (8, wipSummary.getForcastedDateOut());
                pstmt.setString (9, wipSummary.getVendorName());
                pstmt.setString (10, wipSummary.getQtyOut());
                pstmt.setString (11, wipSummary.getDateOut());
                pstmt.setString (12, wipSummary.getAvgGoodDie());
                pstmt.setString (13, wipSummary.getFabLotNumber());
                pstmt.setString (14, wipSummary.getHoldCode());
                pstmt.setString (15, wipSummary.getHoldReason());
                pstmt.setString (16, wipSummary.getHoldDate());
                pstmt.setString (17, wipSummary.getTesterNo());
                pstmt.setString (18, wipSummary.getOwner());
                pstmt.setString (19, wipSummary.getShipYield() );
                pstmt.setString (20, wipSummary.getCustomerWO() );
                pstmt.setString (21, wipSummary.getGrossDie() );
                pstmt.setString (22, wipSummary.getWaferID() );
                pstmt.setString (23, wipSummary.getLotCategory() );
                pstmt.setString (24, wipSummary.getFlow() );
                pstmt.setString (25, wipSummary.getTestProgram() );
                pstmt.setString (26, wipSummary.getFileDateIn() );

                pstmt.addBatch();
            }

            log ("\nSaving Sort Lot Information...");
            pstmt.executeBatch();
            log ("done");

            success = true;
        }
        catch (Exception sqle)
        {
            sqle.printStackTrace();
            throw new ScalarException ("Could not Save Sort lot information", sqle);
        }

        finally
        {
            // Commit/Rollback here it selef -- if its not in a manual transaction
            if (dbTransaction == null)
            {
                try
                {
                    if (conn1 != null)
                    {
                        if (success == true)
                        {
                            conn1.commit();
                        }
                        else
                        {
                            conn1.rollback();
                        }
                    }
                }
                catch (SQLException ignore) {}
            }

            if (pstmt != null)
            {
                try
                {
                    pstmt.close();
                }
                catch (SQLException ignore){}
            }
            if (conn1 != null)
            {
                try
                {
                    conn1.close();
                }
                catch (SQLException ignore){}
            }
        }
    }
	/*
	This method is added by Bhaskar on 20 Jan 2004 to avoid the problem of an unnecessary record entry in sort_lot_history table
	*/
    public static void saveSortLotBinDataEntry(DBTransaction dbTransaction, SortLotBinData lotBinData, String lotId, String processStepId) throws ScalarException {

        Connection conn1 = null;
        PreparedStatement pstmt1 = null;
	    ResultSet rs = null;

        boolean success = false;

        if (lotBinData == null) {
            return;
        }

		if (lotId == null)
		{
			return;
		}

        try {

            // Get the connection from regular datasource if its not in a manual transaction
            if (dbTransaction == null) {
                conn1 = DataSource.getConnection();
            }
            else {
                conn1 = dbTransaction.getConnection();
            }

		    int lot_id = 0;
            int processStepIdInt = 0;
            int mrbLimitInt = 0;
            int rmaLimitInt = 0;
            int downBinLimitInt = 0;
            String mrbLimitStr = null;
            String rmaLimitStr = null;
            String downBinLimitStr = null;
			try
			{
				lot_id = Integer.parseInt(lotId);
                processStepIdInt = Integer.parseInt( processStepId);
			}
			catch (Exception excp)
			{
				System.out.println (excp.getMessage());
				excp.printStackTrace();
			}

            pstmt1 = conn1.prepareStatement("select Parameter_Value from process_parameter where parameter_key like ? and processStep_id = ?");
            pstmt1.setString(1, "MRB Limit");
            pstmt1.setInt( 2, processStepIdInt);
            rs = pstmt1.executeQuery();
            if( rs.next() )
            {
                mrbLimitStr = rs.getString(1);
            }

            rs.close();
            rs = null;
            pstmt1.setString(1, "RMA Limit");
            pstmt1.setInt( 2, processStepIdInt);
            rs = pstmt1.executeQuery();
            if( rs.next() )
            {
                rmaLimitStr = rs.getString(1);
            }

            rs.close();
            rs = null;
            pstmt1.setString(1, "Down%");
            pstmt1.setInt( 2, processStepIdInt);
            rs = pstmt1.executeQuery();
            if( rs.next() )
            {
                downBinLimitStr = rs.getString(1);
            }

            try
            {
                mrbLimitInt = Integer.parseInt( mrbLimitStr);
            }
            catch( NumberFormatException e )
            {
                mrbLimitInt = 1;
            }
            try
            {
                rmaLimitInt = Integer.parseInt( rmaLimitStr);
            }
            catch ( NumberFormatException e )
            {
                rmaLimitInt = 1;
            }
            try
            {
                downBinLimitInt = Integer.parseInt( downBinLimitStr);
            }
            catch( NumberFormatException e)
            {
                downBinLimitInt = 1;
            }

		    log("Lot_Number = "+lotBinData.getLotId()+"; Lot_ID = "+lot_id);

		    if(lot_id == 0){
			    return;
		    }

		    if(pstmt1 != null){
			    pstmt1.close();
    			pstmt1 =  null;
	    	}

		    pstmt1 = conn1.prepareStatement (SqlQueryMappings.get ("adaptor.saveSortLotBinDataEntry"));

		    ArrayList waferIdList = lotBinData.getWaferIdList();
		    ArrayList binList = lotBinData.getBinList();

    		int size = waferIdList.size();

            for(int i=0; i<size; i++) {
                int mrbLimit = 1;
                int rmaLimit = 1;
                int downBinLimit = 1;

                ArrayList temp_it = (ArrayList)waferIdList.get(i);
	    	    String temp = null;

                pstmt1.setString (1, lot_id+"");			//Lot_ID
		        String waferId = (String)temp_it.get(0);

		        try{
                	 waferId = (int)Float.parseFloat(waferId)+"";
		        }
		        catch(NumberFormatException nf){
		        }
		        pstmt1.setString (2, waferId);	//Wafer_ID
                pstmt1.setString (3, (String)temp_it.get(1));	//Test_Time

                try{
                    temp = ((String)temp_it.get(2)).trim();
                    temp = (int)Float.parseFloat(temp)+"";
                    if( Integer.parseInt( temp) <= rmaLimitInt )
                    {
                        rmaLimit = 0;
                    }
                    else if ( Integer.parseInt( temp) > rmaLimitInt && Integer.parseInt( temp) <= mrbLimitInt )
                    {
                        mrbLimit = 0;
                    }
                    else if ( Integer.parseInt( temp) > mrbLimitInt && Integer.parseInt( temp) <= downBinLimitInt )
                    {
                        downBinLimit = 0;
                    }
                }catch(Exception ex ){
                    temp = "0";
                }
                pstmt1.setInt (4, (int)Float.parseFloat(temp));	//Good Dice

                pstmt1.setString (5, (String)temp_it.get(3));	//Yield

                try{
                    temp = (String)temp_it.get(4);
                    temp = (int)Float.parseFloat(temp)+"";
                }catch(Exception ex ){
                    temp = "0";
                }
                pstmt1.setInt (6, (int)Float.parseFloat(temp));	//Bin_1

		        ArrayList temp_binList = (ArrayList)binList.get(i);

                try{
                    temp = (String)temp_binList.get(1);
                    temp = (int)Float.parseFloat(temp)+"";
                }catch(Exception ex ){
                    temp = "0";
                }
                pstmt1.setInt (7, (int)Float.parseFloat(temp));	//Bin_2

                try{
                    temp = (String)temp_binList.get(2);
                    temp = (int)Float.parseFloat(temp)+"";
                }catch(Exception ex ){
                    temp = "0";
                }
                pstmt1.setInt (8, (int)Float.parseFloat(temp));	//Bin_3

                try{
                    temp = (String)temp_binList.get(3);
                    temp = (int)Float.parseFloat(temp)+"";
                }catch(Exception ex ){
                    temp = "0";
                }
                pstmt1.setInt (9, (int)Float.parseFloat(temp));	//Bin_4

                try{
                    temp = (String)temp_binList.get(4);
                    temp = (int)Float.parseFloat(temp)+"";
                }catch(Exception ex ){
                    temp = "0";
                }
                pstmt1.setInt (10, (int)Float.parseFloat(temp));	//Bin_5

                try{
                    temp = (String)temp_binList.get(5);
                    temp = (int)Float.parseFloat(temp)+"";
                }catch(Exception ex ){
                    temp = "0";
                }
                pstmt1.setInt (11, (int)Float.parseFloat(temp));	//Bin_6

                try{
                    temp = (String)temp_binList.get(6);
                    temp = (int)Float.parseFloat(temp)+"";
                }catch(Exception ex ){
                    temp = "0";
                }
                pstmt1.setInt (12, (int)Float.parseFloat(temp));	//Bin_7

                try{
                    temp = (String)temp_binList.get(7);
                    temp = (int)Float.parseFloat(temp)+"";
                }catch(Exception ex ){
                    temp = "0";
                }
                pstmt1.setInt (13, (int)Float.parseFloat(temp));	//Bin_8

                try{
                    temp = (String)temp_binList.get(8);
                    temp = (int)Float.parseFloat(temp)+"";
                }catch(Exception ex ){
                    temp = "0";
                }
                pstmt1.setInt (14, (int)Float.parseFloat(temp));	//Bin_9

                try{
                    temp = (String)temp_binList.get(9);
                    temp = (int)Float.parseFloat(temp)+"";
                }catch(Exception ex ){
                    temp = "0";
                }
                pstmt1.setInt (15, (int)Float.parseFloat(temp));	//Bin_10

                try{
                    temp = (String)temp_binList.get(10);
                    temp = (int)Float.parseFloat(temp)+"";
                }catch(Exception ex ){
                    temp = "0";
                }
                pstmt1.setInt (16, (int)Float.parseFloat(temp));	//Bin_11

                try{
                    temp = (String)temp_binList.get(11);
                    temp = (int)Float.parseFloat(temp)+"";
                }catch(Exception ex ){
                    temp = "0";
                }
                pstmt1.setInt (17, (int)Float.parseFloat(temp));	//Bin_12

		        try{
                    temp = (String)temp_binList.get(12);
                    temp = (int)Float.parseFloat(temp)+"";
                }catch(Exception ex ){
                    temp = "0";
                }
                pstmt1.setInt (18, (int)Float.parseFloat(temp));	//Bin_13

		        try{
                    temp = (String)temp_binList.get(13);
                    temp = (int)Float.parseFloat(temp)+"";
                }catch(Exception ex ){
                    temp = "0";
                }
                pstmt1.setInt (19, (int)Float.parseFloat(temp));	//Bin_14

		        try{
                    temp = (String)temp_binList.get(14);
                    temp = (int)Float.parseFloat(temp)+"";
                }catch(Exception ex ){
                    temp = "0";
                }
                pstmt1.setInt (20, (int)Float.parseFloat(temp));	//Bin_15
				pstmt1.setFloat (21, 0.0f);	//Defect Density
                try
				{
					temp = ((String)temp_it.get(5)).trim();
				}
				catch ( NumberFormatException ex )
				{
					temp = "0";
				}
                try
                {
                    pstmt1.setInt (22, (int)Float.parseFloat(temp)); //Rejected Bin
                }
                catch( NumberFormatException nfe ){
                    pstmt1.setInt (22, 0);
                }

                pstmt1.setInt( 23, mrbLimit);
                pstmt1.setInt( 24, rmaLimit);

                pstmt1.execute();
		    }

            log ("\nSaving Sort Lot Bin Information...");
            //pstmt1.executeBatch();
            log ("done");

            success = true;
        }

        catch (Exception sqle) {
            sqle.printStackTrace();
            throw new ScalarException ("Could not Save Sort lot Bin information", sqle);
        }

        finally {

            // Commit/Rollback here it selef -- if its not in a manual transaction
            if (dbTransaction == null) {
                try {
                    if (conn1 != null) {
                        if (success == true) {
                            conn1.commit();
                        }
                        else {
                            conn1.rollback();
                        }
                    }
                }
                catch (SQLException ignore) {}
            }
		    if(rs != null){
			    try{
				    rs.close();
    		    }
	    		catch(SQLException ignore){}
		    }
            if (pstmt1 != null) {
                try {
                    pstmt1.close();
                }
                catch (SQLException ignore){}
            }
            if (conn1 != null) {
                try {
                    conn1.close();
                }
                catch (SQLException ignore){}
            }
        }
    }
    // USED to store the pcmdata file name and lot number for particular type
    public static void savePCMDATA ( DBTransaction dbTransaction, String lotNumber , String fileType, String filepath ) throws ScalarException {

        Connection conn = null;
        PreparedStatement pstmt = null;
        boolean success = false;

        try {
            // Get the connection from regular datasource if its not in a manual transaction
            if (dbTransaction == null) {
                conn = DataSource.getConnection();
            }
            else {
                conn = dbTransaction.getConnection();
            }

            log ( "\n Saving PCM DATA file path : " + filepath + " and file type " + fileType + " and Lot number " + lotNumber );

            pstmt = conn.prepareStatement (SqlQueryMappings.get ( "adaptor.tsmc.createUpdatePcmData" ));
            pstmt.setString (1, lotNumber);
            pstmt.setString (2, fileType);
            pstmt.setString (3, filepath);
            pstmt.executeUpdate();

            log( "\n done ");
            success = true;

        }
        catch (Exception sqle) {
            sqle.printStackTrace();
            throw new ScalarException ("Could not Save PCM DATA information", sqle);
        }
        finally {
            // Commit/Rollback here it selef -- if its not in a manual transaction
            if (dbTransaction == null) {
                try {
                    if (conn != null) {
                        if (success == true) {
                            conn.commit();
                        }
                        else {
                            conn.rollback();
                        }
                    }
                }
                catch (SQLException ignore) {}
            }

            if (pstmt != null) {
                try {
                    pstmt.close();
                }
                catch (SQLException ignore){}
            }
            if (conn != null) {
                try {
                    conn.close();
                }
                catch (SQLException ignore){}
            }
        }
    }

    /**
     * added by murali on 09/07/2004 for saving sort stage information
     * @param dbTransaction
     * @param sortStageIterator
     * @param VendorName
     * @throws ScalarException
     */
     public static void saveSortStageInfo(DBTransaction dbTransaction, Iterator sortStageIterator,String VendorName) throws ScalarException
     {
         Connection conn = null;
         PreparedStatement pstmt = null;
         boolean success = false;
         try
         {
              // Get the connection from regular datasource if its not in a manual transaction
              if (dbTransaction == null)
              {
                        conn = DataSource.getConnection();
               }
               else
               {
                        conn = dbTransaction.getConnection();
               }
               pstmt = conn.prepareStatement (SqlQueryMappings.get ("adaptor.saveSortStageLot"));
               FabSortLot sortStageLot = null;
               log ("Saving Sort Stage Information... ");
               while (sortStageIterator.hasNext())
               {
                   sortStageLot = (FabSortLot) sortStageIterator.next();
                   log (sortStageLot+"\n");
                   pstmt.setString (1, sortStageLot.getCustProduct());
                   pstmt.setString (2, sortStageLot.getLotId());
                   pstmt.setString (3, sortStageLot.getLotStatus());
                   pstmt.setString (4, sortStageLot.getQtyIn());
                   pstmt.setString (5, sortStageLot.getPoNumber());
                   pstmt.setString (6, sortStageLot.getDateIn());
                   pstmt.setString (7, sortStageLot.getStage()); // stage name
                   pstmt.setString (8, sortStageLot.getForcastedDateOut());
                   pstmt.setString (9, sortStageLot.getVendorName());
                   pstmt.setString (10, sortStageLot.getQtyOut());
                   pstmt.setString (11, sortStageLot.getDateOut());
                   pstmt.addBatch();
               }

               pstmt.executeBatch();
               success = true;
           }

           catch (Exception sqle) {
               sqle.printStackTrace();
               throw new ScalarException ("Could not save Test STAGE Information into database", sqle);
           }

           finally {

               // Commit/Rollback here it selef -- if its not in a manual transaction
               if (dbTransaction == null) {
                   try {
                       if (conn != null) {
                           if (success == true) {
                               conn.commit();
                           }
                           else {
                               conn.rollback();
                           }
                       }
                   }
                   catch (SQLException ignore) {}
               }

               if (pstmt != null) {
                   try { pstmt.close(); } catch (SQLException ignore){}
               }
               if (conn != null) {
                   try { conn.close(); } catch (SQLException ignore){}
               }
           }


     }

    private static Logger s_logger = null;

    private static void log (String message) {

        if (message == null) return;

        if (s_logger != null) {
            synchronized (s_logger) {
                // changed by hareesh on 13th January 2004
                //s_logger.debug (message + LINE_SEPARATOR);
                s_logger.logDebug (message + LINE_SEPARATOR);
            }
        }
        else {
            System.out.println (message);
        }
    }

    /**
     * This method is mainly closes the running lots explicitly.Reason for writing this
     * method suppose today if I get the empty list from the Adaptor, those which are running
     * state lots are not closing. we need to explicitly close those type of lots.
     *
     * @param dbTransaction
     * @param psType
     * @param vendorAdaptorName
     * @throws ScalarException
     */
      
    public static void closeFabSortLot(DBTransaction dbTransaction, String psType, String vendorAdaptorName) throws ScalarException
    {
    	Connection conn = null;
    	PreparedStatement pstmt = null;

    	boolean success = false;

    	try {
    		// Get the connection from regular datasource if its not in a manual transaction
    		if (dbTransaction == null)
    		{
    			conn = DataSource.getConnection();
    		}
    		else
    		{
    			conn = dbTransaction.getConnection();
    		}

            if ( vendorAdaptorName != null )
            {
                pstmt = conn.prepareStatement (SqlQueryMappings.get ("adaptor.closeAllWipInventoryLots"));
    			pstmt.setString (1, psType);
    			pstmt.setString (2, vendorAdaptorName);
    			pstmt.executeUpdate();
                pstmt.close();
                pstmt = null;
            }
            else
            {
                pstmt = conn.prepareStatement (SqlQueryMappings.get ("adaptor.closeAllWipInventoryLots"));
                pstmt.setString (1, psType);
                pstmt.setString (2, VENDOR_NAME_CMI);
                pstmt.executeUpdate();
                pstmt.close();
                pstmt = null;

                pstmt = conn.prepareStatement (SqlQueryMappings.get ("adaptor.closeAllWipInventoryLots"));
                pstmt.setString (1, psType);
                pstmt.setString (2, VENDOR_NAME_CML);
                pstmt.executeUpdate();
                pstmt.close();
                pstmt = null;

                pstmt = conn.prepareStatement (SqlQueryMappings.get ("adaptor.closeAllWipInventoryLots"));
                pstmt.setString (1, psType);
                pstmt.setString (2, VENDOR_NAME_CMT);
                pstmt.executeUpdate();
                pstmt.close();
                pstmt = null;
            }
            success = true;
    	}
    	catch (Exception sqle) {
    		sqle.printStackTrace();
    		throw new ScalarException ("Could not save FabSort Lot Information into database", sqle);
    	}

    	finally {

    		// Commit/Rollback here it self -- if its not in a manual transaction
    		if (dbTransaction == null) {
    			try {
    				if (conn != null) {
    					if (success ) {
    						conn.commit();
    					}
    					else {
    						conn.rollback();
    					}
    				}
    			}
    			catch (SQLException ignore) {ignore.printStackTrace();}
    			catch (Exception e){e.printStackTrace();}
    		}

    		if (pstmt != null) {
    			try { pstmt.close(); } catch (SQLException ignore){ignore.printStackTrace();}
    		}

    		if (conn != null) {
    			try { conn.close(); } catch (SQLException ignore){ignore.printStackTrace();}
    		}
    	}
    }

    public static void closeSortLot(DBTransaction dbTransaction, String psType, String vendorAdaptorName) throws ScalarException
    {
    	Connection conn = null;
    	PreparedStatement pstmt = null;

    	boolean success = false;

    	try {
    		// Get the connection from regular datasource if its not in a manual transaction
    		if (dbTransaction == null)
    		{
    			conn = DataSource.getConnection();
    		}
    		else
    		{
    			conn = dbTransaction.getConnection();
    		}

            if ( vendorAdaptorName != null )
            {
                pstmt = conn.prepareStatement (SqlQueryMappings.get ("adaptor.closeAllWipInventoryLots"));
    			pstmt.setString (1, psType);
    			pstmt.setString (2, vendorAdaptorName);
    			pstmt.executeUpdate();
                pstmt.close();
                pstmt = null;
            }
            else
            {
                pstmt = conn.prepareStatement (SqlQueryMappings.get ("adaptor.closeAllWipInventoryLots"));
                pstmt.setString (1, psType);
                pstmt.setString (2, VENDOR_NAME_SCC);
                pstmt.executeUpdate();
                pstmt.close();
                pstmt = null;

                pstmt = conn.prepareStatement (SqlQueryMappings.get ("adaptor.closeAllWipInventoryLots"));
                pstmt.setString (1, psType);
                pstmt.setString (2, VENDOR_NAME_SCK);
                pstmt.executeUpdate();
                pstmt.close();
                pstmt = null;

                pstmt = conn.prepareStatement (SqlQueryMappings.get ("adaptor.closeAllWipInventoryLots"));
                pstmt.setString (1, psType);
                pstmt.setString (2, VENDOR_NAME_SCM);
                pstmt.executeUpdate();
                pstmt.close();
                pstmt = null;

                pstmt = conn.prepareStatement (SqlQueryMappings.get ("adaptor.closeAllWipInventoryLots"));
                pstmt.setString (1, psType);
                pstmt.setString (2, VENDOR_NAME_SCS);
                pstmt.executeUpdate();
                pstmt.close();
                pstmt = null;
            }
            success = true;
    	}
    	catch (Exception sqle) {
    		sqle.printStackTrace();
    		throw new ScalarException ("Could not save FabSort Lot Information into database", sqle);
    	}

    	finally {

    		// Commit/Rollback here it self -- if its not in a manual transaction
    		if (dbTransaction == null) {
    			try {
    				if (conn != null) {
    					if (success ) {
    						conn.commit();
    					}
    					else {
    						conn.rollback();
    					}
    				}
    			}
    			catch (SQLException ignore) {ignore.printStackTrace();}
    			catch (Exception e){e.printStackTrace();}
    		}

    		if (pstmt != null) {
    			try { pstmt.close(); } catch (SQLException ignore){ignore.printStackTrace();}
    		}

    		if (conn != null) {
    			try { conn.close(); } catch (SQLException ignore){ignore.printStackTrace();}
    		}
    	}
    }


    public static void saveMemorySortBinData( DBTransaction dbTransaction, Iterator lotbinIterator ) throws ScalarException
    {
        Connection conn         = null;
        PreparedStatement pstmt = null;
        BinSetup lotBinData     = null;
        boolean success         = false;

        try
        {
            // Get the connection from regular datasource if its not in a manual transaction
            if ( dbTransaction == null )
            {
                conn = DataSource.getConnection();
            }
            else
            {
                conn = dbTransaction.getConnection();
            }


            pstmt = conn.prepareCall( "{ call sp_create_update_memory_sort_lot_bin ( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? ) }" );


            while ( lotbinIterator.hasNext() )
            {
                lotBinData = ( BinSetup ) lotbinIterator.next();

               log("partNumber : " + lotBinData.getPartNumber() + " ; lotNumber : " + lotBinData.getLotNumber() + ";  waferId : "+ lotBinData.getWaferId() + "; Bin1Qty :" + lotBinData.getBin1Qty() + " ; Bin2Qty :" + lotBinData.getBin2Qty() + "; Process_Type : " + lotBinData.getPsTypeName() + "; VendorName : " + lotBinData.getVendorName() + " ; Mrb_Status :" + lotBinData.getMrbStatus() + "; qtyOut : " + lotBinData.getQtyOut() + "   " + lotBinData.getBin1BalanceQty() + "        " + lotBinData.getBin2BalanceQty() );

                pstmt.setString( 1, lotBinData.getPartNumber() );
                pstmt.setString( 2, lotBinData.getLotNumber() );
                pstmt.setInt(    3, lotBinData.getQtyIn() );
                pstmt.setInt(    4, lotBinData.getQtyOut() );
                pstmt.setFloat(  5, lotBinData.getYield() );
                pstmt.setString( 6, lotBinData.getDateIn() );
                pstmt.setString( 7, lotBinData.getDateOut() );
                pstmt.setString( 8, lotBinData.getVendorName() );
                pstmt.setString( 9, lotBinData.getWaferId() );
                pstmt.setString( 10, lotBinData.getBin1Qty() );
                pstmt.setString( 11, lotBinData.getBin2Qty() );
                pstmt.setString( 12, lotBinData.getBin3Qty() );
                pstmt.setString( 13, lotBinData.getPsTypeName() );
                pstmt.setInt   ( 14, lotBinData.getMrbStatus() );
                pstmt.setString( 15, lotBinData.getBin1BalanceQty() );
                pstmt.setString( 16, lotBinData.getBin2BalanceQty() );
                pstmt.setString( 17, lotBinData.getBin3BalanceQty() );

                pstmt.executeUpdate();
            }

            success = true;


            if ( pstmt != null )
            {
                pstmt.close();
                pstmt = null;
            }
        }
        catch ( Exception sqle )
        {
            sqle.printStackTrace();
            throw new ScalarException( "Could not Save Sort lot Bin information", sqle );
        }

        finally
        {

            // Commit/Rollback here it selef -- if its not in a manual transaction
            if ( dbTransaction == null )
            {
                try
                {
                    if ( conn != null )
                    {
                        if ( success == true )
                        {
                            conn.commit();
                        }
                        else
                        {
                            conn.rollback();
                        }
                    }
                }
                catch ( SQLException ignore )
                {
                    ignore.printStackTrace();
                }
            }
            if ( pstmt != null )
            {
                try
                {
                    pstmt.close();
                }
                catch ( SQLException ignore )
                {
                }
            }
            if ( conn != null )
            {
                try
                {
                    conn.close();
                }
                catch ( SQLException ignore )
                {
                }
            }
        }
    }

    public static void saveWipFedSortBinData( DBTransaction dbTransaction, Iterator lotbinIterator ) throws ScalarException
    {
        Connection conn         = null;
        PreparedStatement pstmt = null;
        FabSortLot lotBinData     = null;
        boolean success         = false;

        try
        {
            // Get the connection from regular datasource if its not in a manual transaction
            if ( dbTransaction == null )
            {
                conn = DataSource.getConnection();
            }
            else
            {
                conn = dbTransaction.getConnection();
            }

            pstmt = conn.prepareCall( "{ call sp_save_informative_binning_info( ?, ?, ?, ?, ? ,? ) } " );

            while ( lotbinIterator.hasNext() )
            {
                lotBinData = ( FabSortLot ) lotbinIterator.next();
                log( "LOT_ID = " +lotBinData.getLotId()  + "WAFER ID =" + lotBinData.getWaferID() );
                pstmt.setString( 1, lotBinData.getLotId() );
                pstmt.setString( 2, lotBinData.getWaferID() );
                pstmt.setString( 3, "BIN1" );                   //BIN NUMBER BY DEFAULT SETTING AS "BIN1"
                pstmt.setString( 4, lotBinData.getQtyIn() );
                pstmt.setString( 5, null );
                 pstmt.setInt( 6, lotBinData.getRejectedQtyIn() );

                pstmt.executeUpdate();
            }

            success = true;

            if ( pstmt != null )
            {
                pstmt.close();
                pstmt = null;
            }
        }
        catch ( Exception sqle )
        {
            sqle.printStackTrace();
            throw new ScalarException( "Could not Save Sort lot Bin information", sqle );
        }

        finally
        {
            //Commit/Rollback here it selef -- if its not in a manual transaction
            if ( dbTransaction == null )
            {
                try
                {
                    if ( conn != null )
                    {
                        if ( success == true )
                        {
                            conn.commit();
                        }
                        else
                        {
                            conn.rollback();
                        }
                    }
                }
                catch ( SQLException ignore )
                {
                    ignore.printStackTrace();
                }
            }
            if ( pstmt != null )
            {
                try
                {
                    pstmt.close();
                }
                catch ( SQLException ignore )
                {
                }
            }
            if ( conn != null )
            {
                try
                {
                    conn.close();
                }
                catch ( SQLException ignore )
                {
                }
            }
        }
    }

     /**
      *
      * @param dbTransaction
      * @param lotbinIterator
      * @throws ScalarException
      */
     public static void saveSortBinData(DBTransaction dbTransaction, Iterator lotbinIterator) throws ScalarException
     {
         Connection conn = null;
         PreparedStatement pstmt = null;
         CallableStatement cstmt = null;
         FabSortLot sortLotBinData = null;
         boolean success = false;

         try
         {
             // Get the connection from regular datasource if its not in a manual transaction
             if (dbTransaction == null)
             {
                 conn = DataSource.getConnection();
             }
             else
             {
                 conn = dbTransaction.getConnection();
             }

             cstmt = conn.prepareCall(SqlQueryMappings.get("adaptor.createUpdateSortLotBin"));

             pstmt = conn.prepareStatement("{ call sp_save_informative_binning_info( ?, ?, ?, ?, ?, ? ) }");
             int lot_id = 0;

             while (lotbinIterator.hasNext())
             {
                 sortLotBinData = (FabSortLot) lotbinIterator.next();

                 cstmt.setString(1, sortLotBinData.getCustProduct());
                 cstmt.setString(2, sortLotBinData.getLotId());
                 cstmt.setInt(3, sortLotBinData.getTotalgoodDieQtyIn() );
                 cstmt.setInt(4, sortLotBinData.getTotalprobeQtyIn() );
                 cstmt.setFloat(5, sortLotBinData.getTotalYield() );
                 cstmt.setString(6, sortLotBinData.getDateIn());
                 cstmt.setString(7, sortLotBinData.getVendorName());
                 cstmt.setString(8, WIP_TYPE_SORT );
                 cstmt.setString(9, sortLotBinData.getQtyIn());
                 cstmt.setString(10, sortLotBinData.getTesterNo() );
                 cstmt.setString(11, sortLotBinData.getTestProgram() );
                 cstmt.setInt(12, sortLotBinData.isShipInfoLot() ? 0 : 1 );
                 cstmt.setString(13, sortLotBinData.getDateOut() );
                 cstmt.registerOutParameter(14, java.sql.Types.INTEGER);

                 cstmt.executeQuery();

                 lot_id = cstmt.getInt(14);

                 log("Lot_Number = "+sortLotBinData.getLotId()+"; Lot_ID = "+lot_id + "; Product Name =" + sortLotBinData.getCustProduct()  );

                 if (lot_id > 0)
                 {
                     pstmt.setInt(1, lot_id);
                     pstmt.setInt(2, Integer.parseInt(sortLotBinData.getWaferID().trim() ));
                     pstmt.setString(3, sortLotBinData.getBinNumber());
                     pstmt.setInt(4, sortLotBinData.getGoodDieQtyIn());
                     pstmt.setInt(5, sortLotBinData.getRejectedQtyIn());
                     pstmt.setString(6, sortLotBinData.getShipYield());
                     pstmt.executeUpdate();
                 }
             }

             success = true;

             if (cstmt != null) {
                 cstmt.close();
                 cstmt = null;
             }
             if (pstmt != null) {
                 pstmt.close();
                 pstmt = null;
             }

         }
         catch (Exception sqle) {
             sqle.printStackTrace();
             throw new ScalarException("Could not Save Sort lot Bin information", sqle);
         }

         finally {
             if (dbTransaction == null) {
                 if (cstmt != null) {
                     try {
                         cstmt.close();
                     }
                     catch (Exception e) {
                     }
                 }

                 try {
                     if (conn != null) {
                         if (success == true) {
                             conn.commit();
                         } else {
                             conn.rollback();
                         }
                     }
                 }
                 catch (SQLException ignore) {
                 }
             }
             if (pstmt != null) {
                 try {
                     pstmt.close();
                 }
                 catch (SQLException ignore) {
                 }
             }

             if (conn != null) {
                 try {
                     conn.close();
                 }
                 catch (SQLException ignore) {
                 }
             }
         }
     }

     public static void saveSortBinInfo ( DBTransaction dbTransaction, ArrayList cpBinInfoList, String vendorName ) throws ScalarException
    {
        Connection conn         = null;
        CallableStatement cstmt = null;
        boolean success         = false;

        SortLotBinData cpBinInfo = null;
        try
        {
            if ( dbTransaction != null )
            {
                conn = dbTransaction.getConnection();
            }
            else
            {
                conn = DataSource.getConnection();
            }

            cstmt = conn.prepareCall( "{ CALL SP_CREATE_UPDATE_SORT_BIN_INFO( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?,?, ?, ?, ?, ?, ?, ?, ?, ?, ?,?, ?, ?, ?, ?, ?, ?, ?, ?, ?,?, ?, ?, ?, ?, ?, ?, ?, ?, ?,?, ?, ?, ?, ?, ?, ?, ?, ?, ?,?, ?, ?, ?, ?, ?, ?, ?, ?, ?,?, ?, ?, ?, ?, ?, ?, ?, ?, ?," +
                    "?,?,?,?, ?, ?, ?, ?, ?, ?, ?, ?, ?,?, ?, ?, ?, ?, ?, ?, ?, ?, ?,?, ?, ?, ?, ?, ?, ?, ?, ?, ?,?, ?, ?, ?, ?, ?, ?, ?, ?, ? ) }" );
            int listSize = (cpBinInfoList == null ? 0 : cpBinInfoList.size());

            for ( int i=0; i < listSize; i++ )
            {
                cpBinInfo = (SortLotBinData) cpBinInfoList.get(i);
                //log( cpBinInfo + "" );
                cstmt.setString( 1, cpBinInfo.getProduct() );
                cstmt.setString( 2, cpBinInfo.getLotNumber() );
                cstmt.setString( 3, cpBinInfo.getWaferId() );
                cstmt.setString( 4, cpBinInfo.getDateIn() );
                cstmt.setString( 5,  cpBinInfo.getProbeCard());
                cstmt.setString( 6, cpBinInfo.getTemp() );
                cstmt.setString( 7, cpBinInfo.getProbeId() );
                cstmt.setString( 8, cpBinInfo.getInsertion() );
                cstmt.setString( 9, cpBinInfo.getGrossDice() );
                cstmt.setString( 10, cpBinInfo.getGoodDice() );
                cstmt.setString( 11, cpBinInfo.getShipYield() );
                cstmt.setString( 12, vendorName );
                cstmt.setString( 13, cpBinInfo.getDateOut() );
                cstmt.setString( 14, cpBinInfo.getTestTime() );
                cstmt.setString( 15, cpBinInfo.getVendorLot() );
                int[] binQty = cpBinInfo.getBinQty();
                for ( int k=0; k < 100; k++ )
                {
                    cstmt.setInt( (k+16), binQty[k] );
                }
                cstmt.addBatch();

            }
            cstmt.executeBatch();

            success = true;
        }
        catch ( Exception e )
        {
            throw new ScalarException ( "Could not save Test BINNING INFO", e );
        }
        finally
        {
            if ( dbTransaction == null && conn != null && success )
            {
                try { conn.commit(); } catch ( SQLException ignore ) {}
            }
            if ( dbTransaction == null && conn != null && !success )
            {
                try { conn.rollback(); } catch ( SQLException ignore ) {}
            }
            if ( cstmt != null )
            {
                try { cstmt.close(); } catch (SQLException ignore ) {}
            }
            if ( conn != null )
            {
                 try { conn.close(); } catch( SQLException ignore ) {}
            }
        }
    }
    public static void setLogger (Logger logger) {
        s_logger = logger;
    }

}